package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.entity.Group;
import cn.gson.crm.model.entity.GroupVo;

public class GroupDao {
	
	/**
	 * 新增分班信息
	 * @throws SQLException 
	 */
	public boolean insertGroup(Group group) throws SQLException{
		String sql="insert into `group`(group_clazz_id,group_student_id) value(?,?)";
		return Jdbc.getInstace().executeUpdate(sql, group.getGroupClazzId(),group.getGroupStudentId());
	}
	/**
	 * 转班
	 * @throws SQLException 
	 */
	public boolean updateGroup(String id,String name) throws SQLException{
		String sql="update `group` set group_clazz_id =? where group_student_id=?";
		return Jdbc.getInstace().executeUpdate(sql, name,id);
	}
	/**
	 * 查询分班信息
	 * @throws SQLException 
	 */
	public List<GroupVo> getGroupBy() throws SQLException{
		String sql="select count(group_clazz_id) count, b.clazz_name clazz_name,c.staff_name  staff_name from `group`"
				+ " a right join clazz b on a.group_clazz_id=b.clazz_id "
				+ " INNER JOIN staff c on b.clazz_teacher_id=c.staff_id GROUP BY clazz_name ";
		List<Map<String,Object>> map=Jdbc.getInstace().executeQueryForList(sql);
		List<GroupVo> listgroup=null;
		if(map!=null&&map.size()>0){
			listgroup=new ArrayList<GroupVo>();
			for (Map<String,Object> gro : map) {
				listgroup.add(buid(gro));
			}
		}
		return listgroup;
		
	}
	/**
	 * 根据班级查询学生
	 * @throws SQLException 
	 */
	public List<GroupVo> getStudentNameByClazzName(String name) throws SQLException{
		String sql="select a.clazz_name clazz_name,c.student_id student_id,c.student_name student_name from clazz a  left join  `group` b on a.clazz_id=b.group_clazz_id"
				+ " left join student c on b.group_student_id=c.student_id where a.clazz_name=? ";
		List<Map<String,Object>> map=Jdbc.getInstace().executeQueryForList(sql,name);
		List<GroupVo> listgroup=null;
		if(map!=null&&map.size()>0){
			listgroup=new ArrayList<GroupVo>();
			for (Map<String,Object> gro : map) {
				listgroup.add(buid(gro));
			}
		}
		return listgroup;
		
	}
	/**
	 * 绑定参数
	 * 
	 */
	private GroupVo buid(Map<String,Object> data){
		GroupVo gr=new GroupVo();
		if(data.get("group_id")!=null){
			gr.setGroupId(Integer.parseInt(data.get("group_id").toString()));
		}
		if(data.get("group_clazz_id")!=null){
			gr.setGroupClazzId(Integer.parseInt(data.get("group_clazz_id").toString()));
		}
		if(data.get("group_student_id")!=null){
			gr.setGroupStudentId(Integer.parseInt(data.get("group_student_id").toString()));
		}
		if(data.get("count")!=null){
			gr.setCount(Integer.parseInt(data.get("count").toString()));
		}
		if(data.get("clazz_name")!=null){
			gr.setClazzName(data.get("clazz_name").toString());
		}
		if(data.get("staff_name")!=null){
			gr.setTeacherName(data.get("staff_name").toString());
		}
		if(data.get("student_name")!=null){
			gr.setStudentName(data.get("student_name").toString());
		}
		if(data.get("student_id")!=null){
			gr.setStudentId(Integer.parseInt(data.get("student_id").toString()));
		}
		return gr;
	}
}
